import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.sqlite.SQLiteDataSource;

public class CalcRisk {
	private static final int CALC_RANGE = 12;

	public static void main(String[] args) throws SQLException {
		SQLiteDataSource ds = new SQLiteDataSource();

		ds.setUrl("jdbc:sqlite:risk.db");
		Connection conn = ds.getConnection();

		conn.createStatement().executeUpdate("delete from risk");
		ResultSet rsTarget = conn.createStatement().executeQuery("select distinct symbol from price");
		PreparedStatement selectPrice = conn.prepareStatement("select a.pricedate, a.end, b.end "
				+ "from price a inner join price b on a.pricedate=b.pricedate and b.symbol='998405.t' where a.symbol = ? order by a.pricedate");
		PreparedStatement insertRisk = conn.prepareStatement("insert into risk values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

		conn.setAutoCommit(false);

		while (rsTarget.next()) {
			String symbol = rsTarget.getString(1);

			selectPrice.setString(1, symbol);

			ResultSet rsPrice = selectPrice.executeQuery();
			double priceprev = 0;
			double tpxprev = 0;
			int idx = 0;
			double[] priceList = new double[CALC_RANGE];
			double[] tpxList = new double[CALC_RANGE];

			while (rsPrice.next()) {
				String date = rsPrice.getString(1);
				double price = rsPrice.getDouble(2);
				double tpx = rsPrice.getDouble(3);

				if (priceprev > 0) {
					double ret = price / priceprev - 1.0;
					double tpret = tpx / tpxprev - 1.0;
					double avg = 0;
					double std = 0;
					double sr = 0;
					double beta = 0;
					double te = 0;
					double stdself = 0;
					double corr = 0;
					double ir = 0;
					priceList[idx % CALC_RANGE] = ret;
					tpxList[idx % CALC_RANGE] = tpret;

					if (idx >= 11) {
						double sum = 0;
						double sumtpx = 0;

						for (int i = 0; i < CALC_RANGE; i++) {
							sum += priceList[i];
							sumtpx += tpxList[i];
						}

						avg = sum / CALC_RANGE;
						double tpavg = sumtpx / CALC_RANGE;
						double var = 0;
						double tpvar = 0;
						double covar = 0;

						for (int i = 0; i < CALC_RANGE; i++) {
							double pricedif = priceList[i] - avg;
							var += Math.pow(pricedif, 2);
							double tpxdif = tpxList[i] - tpavg;
							tpvar += Math.pow(tpxdif, 2);
							covar += pricedif * tpxdif;
						}

						std = Math.sqrt(var);
						double tpstd = Math.sqrt(tpvar);
						sr = avg / std;
						beta = covar / tpvar;
						corr = covar / (tpstd * std);
						te = Math.sqrt(var + tpvar - 2 * corr * std * tpstd);
						stdself = Math.sqrt(var / (Math.pow(beta, 2) * tpvar));
						ir = (avg - tpavg) / te;
					}

					insertRisk.setString(1, date);
					insertRisk.setString(2, symbol);
					insertRisk.setDouble(3, ret);
					insertRisk.setDouble(4, avg * CALC_RANGE);
					insertRisk.setDouble(5, std * Math.sqrt(CALC_RANGE));
					insertRisk.setDouble(6, sr);
					insertRisk.setDouble(7, beta);
					insertRisk.setDouble(8, te);
					insertRisk.setDouble(9, stdself);
					insertRisk.setDouble(10, corr);
					insertRisk.setDouble(11, ir);

					insertRisk.executeUpdate();
				}

				priceprev = price;
				tpxprev = tpx;
				idx++;
			}

			System.out.println(symbol + " done.");
			rsPrice.close();
		}

		rsTarget.close();
		conn.commit();
		conn.close();
		System.out.println("finished.");
	}
}
